import math
import os
import re
from datetime import datetime, timedelta

import numpy as np
import pandas as pd
from PyQt5.QtWidgets import QMessageBox
from openpyxl.chart import BarChart, Reference
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Border, Side, Font, Alignment, PatternFill, colors
from openpyxl.utils import get_column_letter
from openpyxl.workbook import Workbook

thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)

# 创建并设置大标题的字体样式，24加粗
header_font = Font()
header_font.name = 'Arial'
header_font.size = 24
header_font.bold = True

# 定义标题的字体样式，20不加粗
title_font = Font()
title_font.name = 'Arial'
title_font.size = 20

# 定义字体样式，16不加粗
text1_font = Font()
text1_font.name = 'Arial'
text1_font.size = 16

# 定义字体样式，16加粗
text2_font = Font()
text2_font.name = 'Arial'
text2_font.size = 16
text2_font.bold = True

# 设置单元格对齐方式，居中对齐
center_center_alignment = Alignment()
center_center_alignment.horizontal = 'center'
center_center_alignment.vertical = 'center'

# 设置单元格对齐方式，左对齐
left_center_alignment = Alignment()
left_center_alignment.horizontal = 'left'
left_center_alignment.vertical = 'center'

# 设置单元格对齐方式，右对齐
right_center_alignment = Alignment()
right_center_alignment.horizontal = 'right'
right_center_alignment.vertical = 'center'

# 设置单元格对齐方式，左下对齐
left_bottom_alignment = Alignment()
left_bottom_alignment.horizontal = 'left'
left_bottom_alignment.vertical = 'bottom'

# 设置单元格对齐方式，右下对齐
right_bottom_alignment = Alignment()
right_bottom_alignment.horizontal = 'right'
right_bottom_alignment.vertical = 'bottom'

# 定义背景颜色，浅绿色
text1_fill_pattern = PatternFill()
text1_fill_pattern.start_color = colors.Color(rgb='E2EFDA')
text1_fill_pattern.end_color = colors.Color(rgb='E2EFDA')
text1_fill_pattern.fill_type = 'solid'

# 定义背景颜色，浅橙色
text2_fill_pattern = PatternFill()
text2_fill_pattern.start_color = colors.Color(rgb='FCE4D6')
text2_fill_pattern.end_color = colors.Color(rgb='FCE4D6')
text2_fill_pattern.fill_type = 'solid'

blue_font = Font(color="ff2972f4", name='Arial', size=20, bold=True)  # 蓝色字体
# blue_font.color = "#2972f4"
red_font = Font(color="fff30000", name='Arial', size=20, bold=True)  # 红色字体
# red_font.color = "#f30000"
purple_font = Font(color="ff9a38d7", name='Arial', size=20, bold=True)  # 紫色字体
# red_font.color = "#9a38d7"

rule_blue = CellIsRule(operator='greaterThan', formula=['0'], font=blue_font)
rule_red = CellIsRule(operator='lessThan', formula=['0'], font=red_font)

# 后座
backseat = (
    '后腿肉', '后腿肉-精瘦', '后腿肉-精瘦肉丝', '后腿肉-精瘦肉片', '后腿肉-精瘦肉沫',
    # '整猪后座',
    '带骨后座A',
    '带骨带蹄后座',
    '带皮后段(去蹄)', '后腿瘦肉',
    '后腿肉丝', '后腿肉丝-肥瘦分开',
    '后腿肉片-带皮', '后腿肉片-去皮', '后腿肉片-肥瘦分开',
    '后座底板',
    '后腿肉沫',
    '里脊肉', '里脊肉丝', '里脊肉片', '小里脊-腰条', '带膘里脊',  # 里脊
    '纯瘦肉',
    '整后座带蹄',
    '去肘后段',
)
# 前夹
front_clip = (
    '前夹肉',
    '前夹肉切小块-带皮',
    '前夹肉-精瘦',
    '前夹肉-去皮',
    '前夹肉-去皮-肥瘦分开',
    # '整猪前夹',
    '带骨前夹A',
    '去前段带前排',
    '带骨带蹄前夹',
    '前夹肉丝',
    '前夹肉丝-肥瘦分开',
    '前夹肉丝-精瘦',
    '前夹肉片-带皮',
    '前夹肉片-去皮',
    '前夹肉片-纯瘦',
    '前夹肉沫',
    '前夹带骨带前排',
    '肥肉',
    '肥膘',
    '肥膘M',  # 前夹
    '前段带前排带蹄',
    '包心肉',  # 包心肉
)
# 排骨
spareribs = (
    '1中排', '1中排-剁小块', '1中排-剁条',
    '大中段', '打膘中段',
    '整猪中方',
    '1通排', '1通排-剁小块',
    '有颈通排',
    '脊骨', '脊骨-剁小块',
    '扇子骨', '扇子骨-剁小块',
    '猪杂骨',
    '筒子骨', '筒子骨-切小段', '筒子骨-敲断',  # 筒子骨
    '隔子肉',  # 隔子肉
    '前排',
    '去颈骨带尾骨通排',
    '带尾叉通排',
    '尾叉骨', '尾叉骨-剁小块',
)
# 猪蹄
pig_trotters = (
    '前猪蹄', '前猪蹄-剁块', '前猪蹄-烧毛',
    '后猪蹄', '后猪蹄-剁块', '后猪蹄-烧毛',
    '一对猪蹄', '一对猪蹄-烧毛',
    '前后蹄(对蹄)', '前后蹄(对蹄)-烧毛',
    '猪蹄髈', '猪蹄髈-剁小块',
    '猪手', '猪手-剁小块', '猪手-劈两半'
)
# 五花
streaky = (
    '精品五花', '精品五花-带皮切片', '精品五花-红烧肉块', '精品五花-切条',
    '精修大五花a',
    # '里脊肉', '里脊肉丝', '里脊肉片', '小里脊-腰条', '带膘里脊',  # 里脊
)
# 白条
white_strips = (
    '一二级精白条', '三级精白条', '一二级毛白条', '三级毛白条',
    '前中后段不带蹄', '白条不带蹄', '中后段不带蹄',
    '一二级精白条前中段', '前中段带蹄', '前中段不带蹄', '中后段带蹄',
    '黑猪带板油带猪脚去槽头下泡白条'
)
# 副产品
by_product = (
    '1猪腰', '猪肝', '猪肚', '猪大肠', '猪小肠', '猪心肺', '1猪板油', '1猪板油-切小块', '1猪血', '1猪血-盒装', '猪肝-切片',
    '肺叶', '猪-心',
    '脆骨',  # 脆骨
    '预制肥肠',
    '2蛋鸡',  # 蛋鸡
    '2三黄鸡',  # 蛋鸡
    '碎肉',  # 碎肉

    # '纯瘦肉',
    '蹄膀M', '猪皮M',
    '猪耳朵M',
    '狗猪头', '平猪头', '脑花', '带耳头皮', '不带耳头皮', '带肉中耳', '精耳片', '毛口条', '精口条', '猪脸', '毛狗猪头',
    '毛平猪头',  # 猪头
    'o2o打包费', 'o2o配送费', 'o2o其他费用', 'o2o外卖'  # 其他类别
)

# # 路线对应运货司机
# delivery_driver = {
#     '2': '谭克发',
#     '3': '刘烁',
#     '33': '陈涛',
#     '7': '胡荣华',
#     '4': '刘先华',
#     '42': '胡荣华',
#     '5': '何洲斌,',
#     '6': '朱敬文',
#     '62': '李启进,禹师傅',
#     '': None,
# }

keywords_to_exclude = [r'\*', '-', '1队', '2队', '3队', '#', '&']


# Analyze slaughter schedule
# 分析屠宰计划表

# 定义展示信息
def show_warning_message(self, warning_type):
    """展示警告消息"""
    if warning_type == 'file_selection':
        QMessageBox.warning(self, '警告', self.FILE_SELECTION_WARNING)
    elif warning_type == 'wrong_file_format':
        QMessageBox.warning(self, '警告', self.WRONG_FILE_FORMAT_WARNING)


# 新增方法：检查文件是否为Excel格式
def _is_excel_file(self, file_path):
    _, extension = os.path.splitext(file_path)
    return extension.lower() in self.ACCEPTED_EXCEL_EXTENSIONS


# 检查并返回Excel文件路径
def check_and_return_excel_path(self):
    self.file_path = self.textEdit.toPlainText()
    if self.file_path == '':
        QMessageBox.warning(self, '警告', '请添加(订单状态跟踪表)文件！')
        return None
    # 如果路径中包含特定分隔符序列，则仅保留最后一部分
    if self.PATH_SEPARATOR in self.file_path:
        self.file_path = self.file_path.split(self.PATH_SEPARATOR)[-1]

    file_path = self.file_path
    if not file_path:
        show_warning_message(self, self.FILE_SELECTION_WARNING)
        return None

    if _is_excel_file(self, file_path):
        return file_path
    else:
        show_warning_message(self, self.WRONG_FILE_FORMAT_WARNING)
        return None


# 获取 商超餐饮(司机送货表) 所有数据
def get_all_excel_data(self):
    file_path = check_and_return_excel_path(self)
    if file_path:
        data_frames = pd.read_excel(file_path, sheet_name=None)
        # 假设Excel只有一个工作表，可以直接访问第一个数据框
        if data_frames:
            df = list(data_frames.values())[0]  # 这里取第一个工作表的数据，如果有多个工作表，需要根据实际情况选择
            return df
        else:
            QMessageBox.warning(self, '警告', 'Excel中没有数据')
            return None
    else:
        return None


# 处理 配货数量单位
def safe_extract_and_update_company(data_row):
    # 提取单位信息
    if pd.notnull(data_row.iloc[6]):  # 第七列
        match = re.search(r'/([^/]+)$', str(data_row.iloc[6]))
        if match:
            company = match.group(1)
            if company == '边':
                company = '头'
            result = company
        else:
            # 如果没有找到匹配，可以定义一个默认行为
            result = '(未知单位)'
    else:
        result = data_row.iloc[5]

    return result


# 处理 汇总计算
def summary_calculation(all_datas):
    # 首先，确保我们有一个原始DataFrame的深拷贝
    all_datas_copy = all_datas.copy(deep=True)

    # 确保第五列和第12列存在且可以进行字符串操作
    if 'Unnamed: 4' in all_datas_copy.columns and 'Unnamed: 11' in all_datas_copy.columns:
        # # 使用split的n参数确保仅根据第一个'-'进行分割
        # all_datas_copy[['New_Column_5', 'New_Column_12']] = all_datas_copy.iloc[:, 4].str.split('-', n=1, expand=True)

        # 进行拆分操作
        split_result = all_datas_copy.iloc[:, 4].str.split('-', n=1, expand=True)

        # 若列数不足 2，填充占位符
        if split_result.shape[1] < 2:
            split_result[1] = None

        # 截取前两列
        split_result = split_result.iloc[:, :2]

        # 进行赋值操作
        all_datas_copy[['New_Column_5', 'New_Column_12']] = split_result

        all_datas_copy['Unnamed: 4'] = all_datas_copy['New_Column_5']

        # 将分割出来的第二部分数据添加到第12列的原始数据后面，假设第12列是字符串类型
        all_datas_copy['Unnamed: 11'] = all_datas_copy['Unnamed: 11'].astype(str) + '-' + all_datas_copy[
            'New_Column_12']

    if 'Unnamed: 4' in all_datas_copy.columns:
        all_datas_copy.loc[all_datas_copy['Unnamed: 4'].str.contains('后腿肉|后座'), 'Unnamed: 4'] = '后座'

        all_datas_copy.loc[all_datas_copy['Unnamed: 4'].str.contains('前夹肉|前夹'), 'Unnamed: 4'] = '前夹'

        all_datas_copy.loc[all_datas_copy['Unnamed: 4'].str.contains('排'), 'Unnamed: 4'] = '排骨'

        all_datas_copy.loc[all_datas_copy['Unnamed: 4'].str.contains('白条'), 'Unnamed: 4'] = '白条'

        all_datas_copy.loc[all_datas_copy['Unnamed: 4'].str.contains('五花'), 'Unnamed: 4'] = '五花'

    custom_order = ['白条', '排骨', '后座', '前夹', '五花', '前猪蹄', '后猪蹄', '前后蹄(对蹄)', '筒子骨']

    # 创建一个布尔掩码，用于标识'Unnamed: 4'列中值为'后座'或'前夹'的行
    mask = all_datas_copy['Unnamed: 4'].isin(custom_order)

    # 使用布尔掩码来过滤DataFrame，仅保留符合条件的行
    all_datas_copy = all_datas_copy[mask]

    # 获取当前第五列的所有唯一值
    current_values = all_datas_copy.iloc[:, 4].unique()

    # 查找不在允许列表中的值
    missing_values = set(custom_order) - set(current_values)

    # 对于每一个缺失的值，向DataFrame中添加一行
    for value in missing_values:
        # 创建一行新数据，第五列设置为缺失的值，其余列为None
        new_row = [None] * all_datas_copy.shape[1]
        new_row[4] = value  # 假设第五列是索引为4的列
        # 将新行添加到DataFrame
        all_datas_copy = pd.concat([all_datas_copy, pd.DataFrame([new_row], columns=all_datas_copy.columns)],
                                   ignore_index=True)

    sorted_data = all_datas_copy.sort_values(by=[all_datas_copy.columns[4], all_datas_copy.columns[1]])

    sorted_data['group_order'] = sorted_data[sorted_data.columns[4]].map(lambda x: custom_order.index(x))

    groups1 = sorted_data.groupby('group_order')
    up_row = ['总计\品名']
    for group_name, group_data in groups1:
        # 访问每个分组中第五列的数据
        up_row.append(group_data.iloc[0, 4])
    df_first_corrected = pd.DataFrame(up_row).T

    total_line = ['已下单(公斤)']
    grouped_sums7 = sorted_data.groupby('group_order')['Unnamed: 7'].sum()
    for i in range(len(grouped_sums7)):
        total_line.append(round(grouped_sums7.iloc[i], 1))
    df_second_corrected = pd.DataFrame(total_line).T
    df = pd.concat([df_first_corrected, df_second_corrected], ignore_index=True)

    total_line2 = ['转换块数(个)']
    grouped_sums20 = sorted_data.groupby('group_order')['Quantity_Allocated'].sum()
    for i in range(len(grouped_sums7)):
        total_line2.append(round(grouped_sums20.iloc[i], 1))
    df_third_corrected = pd.DataFrame(total_line2).T
    df = pd.concat([df, df_third_corrected], ignore_index=True)

    total_line4 = ['屠宰头数(头)']
    for i in range(len(grouped_sums7)):
        if i == 0:
            total_line4.append(round(grouped_sums20.iloc[i], 1))
        elif i in (1, 2, 3, 4, 8):
            total_line4.append(round(grouped_sums20.iloc[i], 1) / 2)
        elif i == 5:
            total_line4.append(round(grouped_sums20.iloc[i] + grouped_sums20.iloc[i + 2], 1) / 2)
        elif i == 6:
            total_line4.append(round(grouped_sums20.iloc[i] + grouped_sums20.iloc[i + 1], 1) / 2)
        elif i == 7:
            total_line4.append(
                round(grouped_sums20.iloc[i] + grouped_sums20.iloc[i - 1] + grouped_sums20.iloc[i - 2], 1) / 2)
        else:
            total_line4.append(round(grouped_sums20.iloc[i], 1))
    df_five_corrected = pd.DataFrame(total_line4).T
    df = pd.concat([df, df_five_corrected], ignore_index=True)

    custom_order1 = ['排骨', '后座', '前夹', '五花', '前猪蹄', '后猪蹄', '前后蹄(对蹄)', '筒子骨']
    my_i = 1
    for custom in custom_order1:
        number = total_line4[my_i + 1]
        number = math.ceil(number)
        total_line5 = ['%s-建议:' % custom, number]
        total_line6 = ['可下单数(个):', None,
                       round(number * 2 - total_line2[2], 1),
                       round(number * 2 - total_line2[3], 1),
                       round(number * 2 - total_line2[4], 1),
                       round(number * 2 - total_line2[5], 1),
                       round(number * 2 - total_line2[6] - total_line2[8], 1),
                       round(number * 2 - total_line2[7] - total_line2[8], 1),
                       round(number * 2 - total_line2[8] - total_line2[7] - total_line2[6], 1),
                       round(number * 2 - (total_line2[9] if total_line2[9] else 0), 1)]

        df = pd.concat([df, pd.DataFrame(total_line5).T], ignore_index=True)
        df = pd.concat([df, pd.DataFrame(total_line6).T], ignore_index=True)

        my_i = my_i + 1

    return df


# 处理 明细横版
def detail_horizontal_version(all_datas):
    # 首先，确保我们有一个原始DataFrame的深拷贝
    all_datas_copy = all_datas.copy(deep=True)

    # 确保第五列和第12列存在且可以进行字符串操作
    if 'Unnamed: 4' in all_datas_copy.columns and 'Unnamed: 11' in all_datas_copy.columns:
        # # 使用split的n参数确保仅根据第一个'-'进行分割
        # all_datas_copy[['New_Column_5', 'New_Column_12']] = all_datas_copy.iloc[:, 4].str.split('-', n=1, expand=True)

        # 进行拆分操作
        split_result = all_datas_copy.iloc[:, 4].str.split('-', n=1, expand=True)

        # 若列数不足 2，填充占位符
        if split_result.shape[1] < 2:
            split_result[1] = None

        # 截取前两列
        split_result = split_result.iloc[:, :2]

        # 进行赋值操作
        all_datas_copy[['New_Column_5', 'New_Column_12']] = split_result

        all_datas_copy['Unnamed: 4'] = all_datas_copy['New_Column_5']

        # 将分割出来的第二部分数据添加到第12列的原始数据后面，假设第12列是字符串类型
        all_datas_copy['Unnamed: 11'] = all_datas_copy['Unnamed: 11'].astype(str) + '-' + all_datas_copy[
            'New_Column_12']

    # 对第五列进行分组
    groups1 = all_datas_copy.groupby(all_datas_copy.columns[4])
    # 对第二列进行分组
    groups2 = all_datas_copy.groupby(all_datas_copy.columns[1])

    df_new_structure = []

    # 填充df_new_structure，这一步需要根据您的实际数据逻辑调整
    for n2, g2 in groups2:  # 假设对groups2的每个组
        row_data = [n2]  # 为当前groups2的项准备一行数据
        for n1, g1 in groups1:  # 遍历groups1获取对应的值或根据逻辑计算
            # 设置筛选条件
            condition = (all_datas_copy[all_datas_copy.columns[4]] == n1) & (
                    all_datas_copy[all_datas_copy.columns[1]] == n2)

            # 使用条件筛选数据帧
            filtered_rows = all_datas_copy.loc[condition]

            # 提取满足条件的行的第15列数据
            extracted_data = filtered_rows[all_datas_copy.columns[7]]

            # 如果您只想获取第一个匹配项的数据，可以使用：
            # 注意：这里假设sum()是一个示例，实际上可能应为提取特定值，例如first()，具体取决于您的需求
            first_match_data = extracted_data.sum()

            # 将0值替换为None，以便在Excel中不显示
            row_data.append(None if first_match_data == 0 else first_match_data)
        df_new_structure.append(row_data)

    # 使用实际的行和列构建DataFrame
    df_new_corrected = pd.DataFrame(df_new_structure)

    # 然后选择所有数值列进行求和
    numeric_columns = df_new_corrected.select_dtypes(include='number').columns
    sums = df_new_corrected[numeric_columns].sum()

    up_row = ['客户\品名'] + [n2 for n2, _ in groups1]

    # 创建新的合计行，第一列设置为'合计'
    total_row = pd.DataFrame([['合计'] + sums.tolist()])

    # 将新行插入到 DataFrame 开头
    df = pd.concat([pd.DataFrame([up_row]), df_new_corrected], ignore_index=True)

    # 将合计行追加到原始DataFrame
    df = pd.concat([df, total_row], ignore_index=True)

    return df


# 处理 明细竖版
def detail_vertical_version(all_datas):
    # 计算并更新'Quantity_Allocated'列
    all_datas.loc[:, 'Unnamed: 20'] = all_datas.groupby('Unnamed: 4')['Quantity_Allocated'].transform('sum').round(1)
    all_datas.loc[:, 'Unnamed: 19'] = (
        all_datas.groupby('Unnamed: 4')['Unnamed: 7'].transform('sum').round(1).astype('float64'))  # 显式转换为float64类型
    # 应用safe_extract_and_update_company函数到'Quantity_Allocated'列
    all_datas = all_datas.assign(Company=all_datas.apply(safe_extract_and_update_company, axis=1, args=()))

    # 对数据进行排序
    sorted_data = all_datas.sort_values(by=[all_datas.columns[4], all_datas.columns[1]])
    # 重新选择需要的列
    re_sorted_data = sorted_data.iloc[:, [4, 20, 23, 21, 19, 7, 5, 1, 11]]

    return re_sorted_data


# 处理 屠宰计划表 数据
def process_analyze_slaughter_schedule(self):
    all_datas = get_all_excel_data(self)
    if all_datas is not None:
        all_datas = all_datas.iloc[4:]

        # 匹配所有未出货的数据
        all_datas = all_datas[all_datas.iloc[:, 3] == '未出货']

        # 使用assign方法来创建一个新的列或修改现有列，这能减少因数据视图引起的问题
        all_datas = all_datas.assign(Company=all_datas.apply(safe_extract_and_update_company, axis=1, args=()))

        # 在DataFrame中创建一个新列用于存放匹配结果，默认值为None或空字符串
        all_datas['Driver_Name'] = None
        # 在DataFrame中创建一个新列用于存放计算结果，默认值可设置为NaN，配货数量，Quantity of goods allocated
        all_datas['Quantity_Allocated'] = None  # 或者使用 None，取决于后续处理需求

        # 遍历DataFrame的每一行，尝试从第二列提取数字并匹配字典
        for index, row in all_datas.iterrows():
            # 使用正则表达式从第二列提取数字
            match = re.search(r'\d+', str(row.values[1]))
            if match:
                number = match.group()
                # 尝试从字典中获取对应司机的姓名
                driver_name = self.delivery_driver.get(number, '未知司机')
                # 如果找到了匹配的司机姓名，则更新该行的新列
                all_datas.at[index, 'Driver_Name'] = driver_name
            else:
                all_datas.at[index, 'Driver_Name'] = '未知司机'

            if pd.notnull(row.values[6]):
                match = re.search(r'\d+(\.\d+)?', row.values[6])
                # 检查第七列是否有值
                if match:  # 索引从0开始，所以第7列是索引6
                    number = float(match.group())
                    # 如果有值，则进行除法运算，并将结果添加到 result_column 列表中
                    if row.values[4] in ('一二级精白条', '三级精白条', '一二级毛白条', '三级毛白条'):
                        result = round(row.values[7] / 2 / number, 1)
                    else:
                        result = round(row.values[7] / number, 1)
                    all_datas.at[index, 'Quantity_Allocated'] = result
            else:
                # 如果第七列没有值，可以决定如何处理，这里选择添加 NaN
                all_datas.at[index, 'Quantity_Allocated'] = row.values[7]

        # Summary calculation
        # 汇总计算
        summary_calculation_data = summary_calculation(all_datas)

        # Detailed horizontal version
        # 明细竖版
        detail_horizontal_version_data = detail_horizontal_version(all_datas)

        # Detail vertical version
        # 明细横版
        detail_vertical_version_data = detail_vertical_version(all_datas)

        return [summary_calculation_data, detail_horizontal_version_data, detail_vertical_version_data]
    else:
        return None


# 自动调整列宽
def adjust_column_width(worksheet, start_row=1, num_columns=15):
    """根据内容自动调整工作表的指定列数的列宽，从指定行开始计算"""
    for i in range(num_columns):  # 只处理前num_columns列
        column = worksheet[get_column_letter(i + 1)]  # 获取列，如'A', 'B', 'C'...
        max_length = 0
        column_letter = get_column_letter(i + 1)  # 获取列字母
        # 从指定的行开始迭代
        for cell in column[start_row:]:
            try:  # 尝试获取单元格的字符串长度，跳过空值或非字符串类型的错误
                if cell.value is not None:
                    if isinstance(cell.value, float):
                        if np.isnan(cell.value):
                            continue
                        max_length = len(str(round(cell.value, 1)))
                    elif len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                else:
                    if not max_length:
                        max_length = 4
            except TypeError:
                pass
        if num_columns == 24:
            adjusted_width = 3.453 * max_length + 2.461
        elif num_columns == 9:
            if i == 0:
                adjusted_width = 2.72 * max_length + 3.575
            elif i in (1, 2, 3, 4, 5, 6):
                adjusted_width = 0.8007 * max_length + 5.7932
            else:
                adjusted_width = 2.72 * max_length + 3.575
        elif num_columns == 10:
            if i == 0:
                adjusted_width = 34
            elif i == 1:
                adjusted_width = 21.5
            elif i == 2:
                adjusted_width = 16.5
            elif i in (3, 4, 5, 6, 7, 9):
                adjusted_width = 13.5
            elif i == 8:
                adjusted_width = 18.5
            else:
                adjusted_width = 15
        else:
            adjusted_width = 2.67 * max_length + 4.59
        worksheet.column_dimensions[column_letter].width = adjusted_width


# 创建 屠宰计划表 表格
def create_analyze_slaughter_schedule(self, all_datas):
    if all_datas is not None:
        wb = Workbook()

        default_sheet = wb.active
        wb.remove(default_sheet)

        my_index = 0
        for other_data in all_datas:
            if my_index == 0:
                field = '汇总计算'
            elif my_index == 1:
                field = '横版明细'
            elif my_index == 2:
                field = '竖版明细'
            else:
                field = '未知'

            # 创建新的工作表，或者如果已经存在则获取它
            ws = wb.create_sheet(title=field)
            ws.row_dimensions[1].height = 30

            header_name = (datetime.now() + timedelta(hours=8)).strftime('%Y-%m-%d-') + '屠宰计划表[%s]' % field

            # 合并单元格从 'A1' 到 'H1'
            ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(other_data.columns))

            # 写入大标题并应用样式
            ws['A1'] = header_name
            ws['A1'].font = header_font
            ws['A1'].border = thin_border
            ws['A1'].alignment = center_center_alignment

            if my_index == 0:
                i = 2
                # 写入数据
                for index, row in other_data.iterrows():
                    ws.row_dimensions[i].height = 25
                    j = 1
                    for col in row:
                        cell = ws.cell(row=i, column=j, value=col)
                        if i < 6:
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            if j == 1:
                                pass
                            elif j % 2 == 0:
                                cell.fill = text1_fill_pattern
                            else:
                                cell.fill = text2_fill_pattern
                        elif i % 2 == 0:
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            if j == 1:
                                cell.font = red_font
                            elif j == 2:
                                cell.font = purple_font
                        else:
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            cell.font = title_font
                            if j > 1:
                                if col and col > 0:
                                    cell.font = blue_font
                                elif col and col < 0:
                                    cell.font = red_font
                                else:
                                    cell.font = title_font

                        j = j + 1
                    i = i + 1

                ws.row_dimensions[23].height = 30
                ws['A23'] = '计划屠宰(头):'
                ws['A23'].font = red_font
                ws['A23'].alignment = left_center_alignment

                a = other_data.iloc[3][2:].tolist()
                a.sort()
                a = a[1:-1]

                ws['B23'] = math.ceil(sum(a) / len(a))
                ws['B23'].font = purple_font
                ws['B23'].alignment = center_center_alignment

                ws.row_dimensions[24].height = 30

                ws['A24'] = '可下单数(个):'
                ws['A24'].font = blue_font
                # ws['A24'].border = thin_border
                ws['A24'].alignment = left_center_alignment

                ws['C24'] = '=B23*2-C4'
                ws['C24'].font = header_font
                ws['C24'].alignment = center_center_alignment
                # 应用条件格式规则到D24单元格
                ws.conditional_formatting.add('C24', rule_blue)
                ws.conditional_formatting.add('C24', rule_red)

                ws['D24'] = '=B23*2-D4'
                ws['D24'].font = header_font
                ws['D24'].alignment = center_center_alignment
                ws.conditional_formatting.add('D24', rule_blue)
                ws.conditional_formatting.add('D24', rule_red)

                ws['E24'] = '=B23*2-E4'
                ws['E24'].font = header_font
                ws['E24'].alignment = center_center_alignment
                ws.conditional_formatting.add('E24', rule_blue)
                ws.conditional_formatting.add('E24', rule_red)

                ws['F24'] = '=B23*2-F4'
                ws['F24'].font = header_font
                ws['F24'].alignment = center_center_alignment
                ws.conditional_formatting.add('F24', rule_blue)
                ws.conditional_formatting.add('F24', rule_red)

                ws['G24'] = '=B23*2-G4-I4'
                ws['G24'].font = header_font
                ws['G24'].alignment = center_center_alignment
                ws.conditional_formatting.add('G24', rule_blue)
                ws.conditional_formatting.add('G24', rule_red)

                ws['H24'] = '=B23*2-H4-I4'
                ws['H24'].font = header_font
                ws['H24'].alignment = center_center_alignment
                ws.conditional_formatting.add('H24', rule_blue)
                ws.conditional_formatting.add('H24', rule_red)

                ws['I24'] = '=B23*2-I4-G4-H4'
                ws['I24'].font = header_font
                ws['I24'].alignment = center_center_alignment
                ws.conditional_formatting.add('I24', rule_blue)
                ws.conditional_formatting.add('I24', rule_red)

                ws['J24'] = '=B23*2-J4'
                ws['J24'].font = header_font
                ws['J24'].alignment = center_center_alignment
                ws.conditional_formatting.add('J24', rule_blue)
                ws.conditional_formatting.add('J24', rule_red)

                # 第一列数据
                third_data = ['品名'] + other_data.iloc[0][2:10].tolist()
                first_data = ['转换块数'] + other_data.iloc[2][2:10].tolist()
                second_data = ['屠宰块数']

                # 合并三个列表
                combined_data = [third_data, first_data, second_data]

                # 转换为DataFrame
                df_combined = pd.DataFrame(combined_data)

                # 行列转换，假设第二列为索引列
                df_transposed = df_combined.T

                i = i + 4
                # 写入数据
                for index, row in df_transposed.iterrows():
                    ws.row_dimensions[i].height = 25
                    j = 1
                    for col in row:
                        cell = ws.cell(row=i, column=j, value=col)
                        cell.border = thin_border
                        cell.alignment = center_center_alignment
                        cell.font = title_font

                        j = j + 1
                    i = i + 1

                ws['C27'] = '=B23*2'
                ws['C27'].border = thin_border
                ws['C27'].font = title_font
                ws['C27'].alignment = center_center_alignment

                ws['C28'] = '=B23*2'
                ws['C28'].border = thin_border
                ws['C28'].font = title_font
                ws['C28'].alignment = center_center_alignment

                ws['C29'] = '=B23*2'
                ws['C29'].border = thin_border
                ws['C29'].font = title_font
                ws['C29'].alignment = center_center_alignment

                ws['C30'] = '=B23*2'
                ws['C30'].border = thin_border
                ws['C30'].font = title_font
                ws['C30'].alignment = center_center_alignment

                ws['C31'] = '=B23*2'
                ws['C31'].border = thin_border
                ws['C31'].font = title_font
                ws['C31'].alignment = center_center_alignment

                ws['C32'] = '=B23*2'
                ws['C32'].border = thin_border
                ws['C32'].font = title_font
                ws['C32'].alignment = center_center_alignment

                ws['C33'] = '=B23*2'
                ws['C33'].border = thin_border
                ws['C33'].font = title_font
                ws['C33'].alignment = center_center_alignment

                ws['C34'] = '=B23*2'
                ws['C34'].border = thin_border
                ws['C34'].font = title_font
                ws['C34'].alignment = center_center_alignment

                # 定义图表数据区域
                chart_data = Reference(ws, min_col=2, min_row=26, max_col=3, max_row=34)

                # 创建柱状图
                chart = BarChart()
                chart.add_data(chart_data, titles_from_data=True)
                chart.set_categories(Reference(ws, min_col=1, min_row=27, max_row=34))

                # 应用新的宽度和高度
                chart.width = chart.width * 1.11
                chart.height = chart.height * 1.05

                # 设置图表标题
                chart.title = "[转换块数]和[屠宰块数]数据对比"

                # 将图表添加到工作表
                ws.add_chart(chart, "E26")

                adjust_column_width(ws, start_row=1, num_columns=len(other_data.columns))

            elif my_index == 1:
                i = 2
                # 写入数据
                for index, row in other_data.iterrows():
                    if i == 2:
                        ws.row_dimensions[i].height = 25
                    else:
                        ws.row_dimensions[i].height = 20
                    j = 1
                    for col in row:
                        if j == 1:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = left_center_alignment
                        else:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                        j = j + 1
                    i = i + 1
                adjust_column_width(ws, start_row=1, num_columns=len(other_data.columns))

            elif my_index == 2:
                ws.row_dimensions[2].height = 25
                ws.row_dimensions[3].height = 25

                ws['A2'] = "品名"
                ws['A2'].font = title_font
                ws['A2'].border = thin_border
                ws['A2'].alignment = center_center_alignment

                ws['A3'] = "品名"
                ws['A3'].font = title_font
                ws['A3'].border = thin_border
                ws['A3'].alignment = center_center_alignment

                ws['B2'] = "合计"
                ws['B2'].font = title_font
                ws['B2'].border = thin_border
                ws['B2'].alignment = center_center_alignment

                ws['B3'] = "合计"
                ws['B3'].font = title_font
                ws['B3'].border = thin_border
                ws['B3'].alignment = center_center_alignment

                ws['C2'] = "转换块数"
                ws['C2'].font = text2_font
                ws['C2'].border = thin_border
                ws['C2'].alignment = center_center_alignment

                ws['C3'] = "块数"
                ws['C3'].font = text1_font
                ws['C3'].border = thin_border
                ws['C3'].alignment = center_center_alignment

                ws['D2'] = "转换块数"
                ws['D2'].font = title_font
                ws['D2'].border = thin_border
                ws['D2'].alignment = center_center_alignment

                ws['D3'] = "单位"
                ws['D3'].font = text1_font
                ws['D3'].border = thin_border
                ws['D3'].alignment = center_center_alignment

                ws['E2'] = "合计"
                ws['E2'].font = title_font
                ws['E2'].border = thin_border
                ws['E2'].alignment = center_center_alignment

                ws['E3'] = "合计"
                ws['E3'].font = title_font
                ws['E3'].border = thin_border
                ws['E3'].alignment = center_center_alignment

                ws['F2'] = "订单数量"
                ws['F2'].font = text2_font
                ws['F2'].border = thin_border
                ws['F2'].alignment = center_center_alignment

                ws['F3'] = "数量"
                ws['F3'].font = text1_font
                ws['F3'].border = thin_border
                ws['F3'].alignment = center_center_alignment

                ws['G2'] = "订单数量"
                ws['G2'].font = text2_font
                ws['G2'].border = thin_border
                ws['G2'].alignment = center_center_alignment

                ws['G3'] = "单位"
                ws['G3'].font = text1_font
                ws['G3'].border = thin_border
                ws['G3'].alignment = center_center_alignment

                ws['H2'] = "客户名称"
                ws['H2'].font = title_font
                ws['H2'].border = thin_border
                ws['H2'].alignment = center_center_alignment

                ws['H3'] = "客户名称"
                ws['H3'].font = title_font
                ws['H3'].border = thin_border
                ws['H3'].alignment = center_center_alignment

                ws['I2'] = "备注"
                ws['I2'].font = title_font
                ws['I2'].border = thin_border
                ws['I2'].alignment = center_center_alignment

                ws['I3'] = "备注"
                ws['I3'].font = title_font
                ws['I3'].border = thin_border
                ws['I3'].alignment = center_center_alignment

                # 合并成所需单元格样式
                ws.merge_cells(start_row=2, start_column=1, end_row=3, end_column=1)
                ws.merge_cells(start_row=2, start_column=2, end_row=3, end_column=2)
                ws.merge_cells(start_row=2, start_column=3, end_row=2, end_column=4)

                ws.merge_cells(start_row=2, start_column=5, end_row=3, end_column=5)
                ws.merge_cells(start_row=2, start_column=6, end_row=2, end_column=7)
                ws.merge_cells(start_row=2, start_column=8, end_row=3, end_column=8)
                ws.merge_cells(start_row=2, start_column=9, end_row=3, end_column=9)

                i = 4

                # 初始化一个字典来存储需要合并的单元格范围
                merge_ranges2 = {}

                # 写入数据并记录需要合并的单元格
                last_col_a_value2 = None

                for index, row in other_data.iterrows():
                    ws.row_dimensions[i].height = 20
                    j = 1
                    for col in row:
                        if j == 1:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = left_center_alignment
                        elif j == 2:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            cell.fill = text1_fill_pattern
                        elif j == 3:
                            if col is not None:
                                col = col if col % 1 != 0 else int(col)
                            else:
                                col = None
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            cell.fill = text2_fill_pattern
                        elif j == 4:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            cell.fill = text2_fill_pattern
                        elif j == 5:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            cell.fill = text1_fill_pattern
                        elif j == 6:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text2_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            cell.fill = text2_fill_pattern
                        elif j == 7:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text2_font
                            cell.border = thin_border
                            cell.alignment = center_center_alignment
                            cell.fill = text2_fill_pattern
                        elif j == 8:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = left_center_alignment
                        elif j == 9:
                            cell = ws.cell(row=i, column=j, value=col)
                            cell.font = text1_font
                            cell.border = thin_border
                            cell.alignment = left_center_alignment
                        j = j + 1

                    # 检查当前行A列（第一列）的值是否与上一行相同
                    if row.iloc[0] == last_col_a_value2:
                        # 如果相同，记录这个范围需要被合并
                        if row.iloc[0] not in merge_ranges2:
                            merge_ranges2[row.iloc[0]] = [i - 1, i]
                        else:
                            merge_ranges2[row.iloc[0]][1] = i
                    else:
                        # 如果不同，更新上一个值并继续
                        if last_col_a_value2 is not None:
                            ws.merge_cells(start_row=merge_ranges2[last_col_a_value2][0], start_column=1,
                                           end_row=merge_ranges2[last_col_a_value2][1], end_column=1)
                            ws.merge_cells(start_row=merge_ranges2[last_col_a_value2][0], start_column=2,
                                           end_row=merge_ranges2[last_col_a_value2][1], end_column=2)
                            ws.merge_cells(start_row=merge_ranges2[last_col_a_value2][0], start_column=5,
                                           end_row=merge_ranges2[last_col_a_value2][1], end_column=5)
                        last_col_a_value2 = row.iloc[0]
                        merge_ranges2[row.iloc[0]] = [i, i]

                    i = i + 1

                # 确保处理最后一组需要合并的单元格
                if last_col_a_value2 in merge_ranges2:
                    ws.merge_cells(start_row=merge_ranges2[last_col_a_value2][0], start_column=1,
                                   end_row=merge_ranges2[last_col_a_value2][1], end_column=1)
                    ws.merge_cells(start_row=merge_ranges2[last_col_a_value2][0], start_column=2,
                                   end_row=merge_ranges2[last_col_a_value2][1], end_column=2)
                    ws.merge_cells(start_row=merge_ranges2[last_col_a_value2][0], start_column=5,
                                   end_row=merge_ranges2[last_col_a_value2][1], end_column=5)

                adjust_column_width(ws, start_row=3, num_columns=len(other_data.columns))

            my_index = my_index + 1

        # 获取格式化的年月日字符串
        formatted_date = (datetime.now() + timedelta(hours=8)).strftime('%Y%m%d_%H%M%S')
        file_name = '/屠宰计划表_' + formatted_date + '.xls'
        file_path = self.file_path
        pattern = r'(.+?)(?=/[^/]*$)'
        match = re.search(pattern, file_path)
        file_path = match.group(1)
        file_name_path = file_path + file_name

        # 保存文件到路径
        wb.save(file_name_path)

        QMessageBox.warning(self, '提示', '数据导出成功！')
